const express = require('express')
const router = express.Router()
const bodyParser = require('body-parser');
const db = require('../db/db')
router.use(bodyParser.json())
//查询四川省边界接口
router.get('/city/border', (req, res) => {
  const sqlStr = 'SELECT ST_AsGeoJSON(geom) as geojson FROM city_border'
  db.query(sqlStr, (err, results) => {
    if (err) return err.message
    const obj = JSON.parse(results.rows[0].geojson)
    res.json(obj)
  })
})
//查询四川省各市区接口
router.get('/city/polygon', (req, res) => {
  const query = req.query.name
  let sqlStr = ''
  let params = []
  if (query == '' || query == undefined) {
    sqlStr = "SELECT cityname,ST_AsGeoJSON(geom) as geojson,postal FROM city_polygon"
  }
  else {
    sqlStr = 'SELECT cityname,ST_AsGeoJSON(geom) as geojson,postal FROM city_polygon where'
    //查询单个参数转为数组
    if (typeof (query) == 'string') {
      let arr = Array.of(query)
      params = arr
    }
    else {
      params = query
    }
    //根据参数数组生成sql语句
    for (let i = 0; i < params.length; i++) {
      sqlStr = sqlStr + ' cityname = $' + (i + 1)
      if (i < (params.length - 1)) {
        sqlStr = sqlStr + ' or'
      }
    }
  }
  let data = []
  let travelArr = []
  let postal = []
  db.query(sqlStr, params, (err, results) => {
    if (err) return err.message
    let city = results.rows
    for (let i = 0; i < city.length; i++) {
      const obj = {
        cityname: city[i].cityname,
        geojson: JSON.parse(city[i].geojson),
        travel: []
      }
      postal.push(city[i].postal)
      data.push(obj)
    }
    const sqlTravel = 'SELECT * FROM travel'
    db.query(sqlTravel, (err, results) => {
      if (err) return err.message
      let travel = results.rows
      for (let i = 0; i < data.length; i++) {
        travelArr[i] = travel.filter((item) => {
          return item.postal == postal[i]
        })
      }
      for (let i = 0; i < data.length; i++) {
        data[i].travel.push(travelArr[i])
      }
      res.json(data)
    })
  })
})
//查询景区接口
router.get('/scenery/grade', (req, res) => {
  const query = req.query.Grade
  //如果查询参数为A
  if (query == "A") {
    db.query("select * from scenery where grade like '%A'", (err, results) => {
      if (err) return err.message
      res.json(results.rows)
    })
    return;
  }
  //如果没有参数
  if (query == undefined) return
  let sqlStr = 'select * from scenery where'
  let params = []
  //查询单个参数转为数组
  if (typeof (query) == 'string') {
    arr = Array.of(query)
    params = arr
  }
  else {
    params = query
  }
  //根据参数数组生成sql语句
  for (let i = 0; i < params.length; i++) {
    sqlStr = sqlStr + ' grade = $' + (i + 1)
    if (i < (params.length - 1)) {
      sqlStr = sqlStr + ' or'
    }
  }
  db.query(sqlStr, params, (err, results) => {
    if (err) return err.message
    res.json(results.rows)
  })
})
//动态搜索查询接口
router.get('/querys', (req, res) => {
  const table = req.query.table
  const name = req.query.name
  const sqlStr = `select * from ${table} where name LIKE '%${name}%'`
  db.query(sqlStr, (err, results) => {
    if (err) return err.message
    res.json(results.rows)
  })
})
//查询任意两点间的最短路径
router.get('/path/LatLon', (req, res) => {
  let source = req.query.source
  let target = req.query.target
  source = 'point(' + source[0] + ' ' + source[1] + ')'
  target = 'point(' + target[0] + ' ' + target[1] + ')'
  //根据用户输入的经纬度查询距离最近的道路
  const sqlStr = `select * from load ORDER BY ST_Distance( st_geomfromtext ('${source}', 4326),load.geom) limit 1;`
  db.query(sqlStr, (err, results) => {
    if (err) return err.message
    let pathSource = results.rows[0]
    const minSql = `select * from load ORDER BY ST_Distance( st_geomfromtext ('${target}', 4326),load.geom) limit 1;`
    db.query(minSql, (err, results) => {
      if (err) return err.message
      let pathTarget = results.rows[0]
      const djSql = `SELECT load.gid,load.name,ST_AsGeoJSON(load.geom) as geojson,st_length(load.geom) as length
      FROM pgr_dijkstra(
        'SELECT gid AS id,
          source, target,
          cost, reverse_cost
        FROM load',
        ${pathSource.source}, ${pathTarget.target},
        directed := FALSE
      ) as dj
      inner join load on dj.edge=load.gid;`

      db.query(djSql, (err, results) => {
        if (err) return err.message
        let data = []

        for (let i = 0; i < results.rowCount; i++) {
          const obj = {
            gid: results.rows[i].gid,
            name: results.rows[i].name,
            geojson: JSON.parse(results.rows[i].geojson),
            length: results.rows[i].length
          }
          data.push(obj)
        }
        res.json(data)
      })
    })
  })
})
//用户注册接口
router.post('/user/register', (req, res) => {
  const username = req.body.use;
  const password = req.body.pwd;
  const seSql = `select * from users where username='${username}'`
  db.query(seSql, (err, results) => {
    if (err) return err.message
    if (results.rows.length != 0) {
      res.json({ state: 0, msg: '用户名已占用' })
    }
    else {
      let code = '' + (parseInt(Math.random() * 1000000) + 1000000);
      code = code.substring(1, 7);
      const inSql = `INSERT INTO users (code,username,password) VALUES ('${code}','${username}','${password}')`
      db.query(inSql, (err) => {
        if (err) return err.message
        res.json({ state: 1, msg: '注册成功', })
      })
    }
  })
})
//用户登录接口
router.get('/user/login', (req, res) => {
  const username = req.query.use
  const password = req.query.pwd
  const sqlStr = `select * from users where username = '${username}' and password = '${password}'`
  db.query(sqlStr, (err, results) => {
    if (err) return err.message
    let data = results.rows
    if (data.length == 0) {
      res.json({ state: 0, msg: '请输入正确的用户名' })
    }
    else {
      let obj = {
        state: 1,
        msg: '登录成功',
        data: data
      }
      res.json(obj)
    }

  })
})
module.exports = router